package com.example.chapter06.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.chapter06.entity.User;

import java.util.ArrayList;
import java.util.List;

public class UserDBHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "user.db";

    private static final int DB_VERSION = 2;

    private static final String TABLE_NAME = "user_info";


    private static UserDBHelper mHelper = null;

    private SQLiteDatabase mRDB = null;
    private SQLiteDatabase mWDB = null;

    private UserDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    //利用单例模式获取数据库帮助器的唯一实例
    public static UserDBHelper getInstance(Context context){
        if(mHelper == null){
            mHelper = new UserDBHelper(context);
        }
        return mHelper;

    }

    //打开数据库的读链接
    public SQLiteDatabase openReadLink(){
        if(mRDB == null || !mRDB.isOpen()){
            mRDB  = mHelper.getReadableDatabase();
        }
        return mRDB;
    }

    //打开数据库的写连接
    public SQLiteDatabase openWriteLink(){
        if(mWDB == null || !mWDB.isOpen()){
            mWDB  = mHelper.getWritableDatabase();
        }
        return mWDB;
    }

    //关闭数据库链接
    public void closeLink(){
        if(mRDB!=null&&mRDB.isOpen()){
            mRDB.close();
            mRDB = null;
        }

        if(mWDB!=null&&mWDB.isOpen()){
            mWDB.close();
            mWDB = null;
        }
    }

    //创建数据库 执行建表语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE IF NOT EXISTS "+ TABLE_NAME+" (id INTEGER PRIMARY KEY " +
                "AUTOINCREMENT NOT NULL," +
                "name VARCHAR NOT NULL, age INTEGER NOT NULL,height LONG NOT NULL, " +
                "weight FLOAT NOT NULL," +
                "married INTEGER NOT NULL,update_time VARCHAR NOT NULL);";
        db.execSQL(sql);


    }

    //更新数据库的版本  比如修改表结构
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "ALTER TABLE " +TABLE_NAME+" ADD COLUMN phone VARCHAR";
        db.execSQL(sql);

        sql = "ALTER TABLE " +TABLE_NAME+" ADD COLUMN password VARCHAR";
        db.execSQL(sql);
    }

    public long insert(User user){
        ContentValues values = new ContentValues();
        values.put("name",user.name);
        values.put("age",user.age);
        values.put("height",user.height);
        values.put("weight",user.weight);
        values.put("married",user.married);
        values.put("update_time", String.valueOf(user.updateTime));
        //第二个参数：null 防止前面values为空，sql语句不对
        //return mWDB.insert(TABLE_NAME,null,values);

        try {
            mWDB.beginTransaction();
            mWDB.insert(TABLE_NAME,null,values);
            mWDB.insert(TABLE_NAME,null,values);
            int i = 10/0;
            mWDB.setTransactionSuccessful();
        }catch (Exception e){
            Log.e("liu","事务失败");
        }finally {
            mWDB.endTransaction();
        }

        return 1;
    }

    //删除
    public long deleteByName(String name,String age){
        //删除所有
        //mWDB.delete(TABLE_NAME,"1=1",null);
        return mWDB.delete(TABLE_NAME,"name=? and age=?",
                new String[]{name,age});
    }

    //修改
    public long update(User user){
        ContentValues values = new ContentValues();
        values.put("name",user.name);
        values.put("age",user.age);
        values.put("height",user.height);
        values.put("weight",user.weight);
        values.put("married",user.married);
        values.put("update_time", String.valueOf(user.updateTime));
        return mWDB.update(TABLE_NAME,values,"name=?",new String[]{user.name});
    }

    //查询所有
    public List<User> queryAll(){
        List<User> list = new ArrayList<>();
        //执行记录查询动作，该语句返回结果集的游标
        @SuppressLint("Recycle") Cursor cursor = mRDB.query(TABLE_NAME, null, null,
                null, null, null, null);
        //循环取出游标指向的每条记录
        while (cursor.moveToNext()){
            User user = new User();
            user.id = cursor.getInt(0);
            user.name = cursor.getString(1);
            user.age = cursor.getInt(2);
            user.height = cursor.getLong(3);
            user.weight = cursor.getLong(4);
            //SQLite没有布尔型，用0表示false,用1表示true
            user.married = (cursor.getInt(5) != 0);
            list.add(user);
        }
        return list;
    }



    public List<User> queryByName(String name){
        List<User> list = new ArrayList<>();
        //执行记录查询动作，该语句返回结果集的游标
        @SuppressLint("Recycle") Cursor cursor = mRDB.query(TABLE_NAME, null, "name=?",
                new String[]{name}, null, null, null);
        //循环取出游标指向的每条记录
        while (cursor.moveToNext()){
            User user = new User();
            user.id = cursor.getInt(0);
            user.name = cursor.getString(1);
            user.age = cursor.getInt(2);
            user.height = cursor.getLong(3);
            user.weight = cursor.getLong(4);
            //SQLite没有布尔型，用0表示false,用1表示true
            user.married = (cursor.getInt(5) != 0);
            list.add(user);
        }
        return list;
    }

}
